package com.scse.bms.util;

import com.scse.bms.domain.RepositoryVO;
import com.scse.bms.entity.RepositoryDO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;


/**
 * @Title: ExcelUtil
 * @Author: 吴赠禹
 * @Date: 2021/3/4 13:30
 * @Version: 1.0
 * @Description: TODO
 */

@Slf4j
public class ExcelUtil {

    public static void main(String[] args) throws Exception {
        String fileName2 = "d:\\书单.xlsx";
        List<RepositoryVO> list = parseXlsx(fileName2);
        System.out.println(list.toString());
    }

    /**
     * 读取xls格式excel
     * @param fileName
     * @throws Exception
     */
    public static List<RepositoryVO> parseXls(String fileName) throws Exception {
        // 1、读取文件（逐层包装，装饰器模式）
        FileInputStream fis = new FileInputStream(new File(fileName));
        Workbook workbook = new HSSFWorkbook(fis);
        // 2、获取要处理的页面
        Sheet sheet = workbook.getSheetAt(0);
        // 3、处理单元格
        return printCell(sheet);

    }

    /**
     * 读取xlsx格式excel
     * @param fileName
     * @throws Exception
     */
    public static List<RepositoryVO> parseXlsx(String fileName) throws Exception {
        FileInputStream fis = new FileInputStream(new File(fileName));
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        return  printCell(sheet);
    }

    /**
     * 打印单元格
     * @param sheet
     */
    private static List<RepositoryVO> printCell(Sheet sheet) {
        int length = sheet.getRow(1).getLastCellNum();
        String[] rowInfo = new String[length];
        RepositoryVO repositoryVO;
        List<RepositoryVO> list = new ArrayList<>(30);
        // 获取当前页的总行数
        int totalRowNums = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < totalRowNums; i++) {
            // 获取行
            Row row = sheet.getRow(i);
            // 获取列数（格子数）
            int cells = row.getLastCellNum();
            for (int j = 0; j < cells; j++) {
                Cell cell = row.getCell(j);
                rowInfo[j] = getCellValue(cell);
            }
            // 有风险
            try {
                repositoryVO = new RepositoryVO(rowInfo[0], rowInfo[1], rowInfo[2], rowInfo[3], rowInfo[4],
                        rowInfo[5], rowInfo[6], rowInfo[7], rowInfo[8], rowInfo[9]);
                list.add(repositoryVO);
            } catch (ArrayIndexOutOfBoundsException e) {
                log.info("[printCell] 发生异常!" + e.getMessage());
            }
        }
        return list;
    }

    /**
     * 获取单元格的值，此处做简单处理
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        // 处理空值
        if (cell == null) {
            return "";
        }
        // 部分API已过时，此处是更新后的
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        }
        if (cellType == CellType.NUMERIC) {
            // 返回数值类型的值
            Object inputValue = null;
            Long longVal = Math.round(cell.getNumericCellValue());
            double doubleVal = cell.getNumericCellValue();
            if(Double.parseDouble(longVal + ".0") == doubleVal){
                inputValue = longVal;
            }
            else{
                inputValue = doubleVal;
            }
            DecimalFormat df = new DecimalFormat("#.####");
            return df.format(inputValue);
        }
        return cell.getStringCellValue();
    }

}


